package com.example.attendancesystem.Tools;


import com.example.attendancesystem.Models.Employee;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.springframework.util.ClassUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;

/**
 * excel的相关操作
 */
public class Excel {

    /**
     * 创建员工信息Excel表格
     * @param employeeList 员工信息集合
     */
    public static void creatStudentExcel(List<Employee> employeeList, String fileName) {
        //1、表格的头
        String resourcePath = ClassUtils.getDefaultClassLoader().getResource("").getPath();
        String savePath = resourcePath + "/static/downLoad";
        File path = new File(savePath);
        //2、路径不存在 创建一个目录
        if (!path.exists()) path.mkdirs();
        //3、要写入的文件
        File file = new File(resourcePath+"/static/downLoad/"+fileName);
        try {
            //4、如果文件存在，存删除文件
            if (file.exists()) file.delete();
            //5、创建要写入新文件
            file.createNewFile();
            WritableWorkbook workbook = Workbook.createWorkbook(file);
            WritableSheet sheetA = workbook.createSheet("sheet1", 0);
            Label labelA;
            String[] title = {"ID", "工号", "姓名", "性别","联系方式", "入职时间", "邮箱", "住址", "最高学历", "职务","部门","出生日期"};
            //6、向Excel表格第一行写入标题
            for (int i = 0; i < title.length; i++) {
                labelA = new Label(i, 0, title[i]);
                sheetA.addCell(labelA);
            }
            //7、向Excel表格写入数据
            int i = 1;
           //String[] title = {"ID", "工号", "姓名", "性别","联系方式", "入职时间", "邮箱", "住址", "最高学历", "职务","部门","出生日期"};
            for (Employee employee : employeeList) {
                labelA = new Label(0, i, "" + employee.getEmpId());
                sheetA.addCell(labelA);
                labelA = new Label(1, i, "" + employee.getEmpNumber());
                sheetA.addCell(labelA);
                labelA = new Label(2, i, "" + employee.getEmpName());
                sheetA.addCell(labelA);
                labelA = new Label(3, i, "" + employee.getEmpGender());
                sheetA.addCell(labelA);
                labelA = new Label(4, i, "" + employee.getEmpTel());
                sheetA.addCell(labelA);
                labelA = new Label(5, i, "" + employee.getAddTime());
                sheetA.addCell(labelA);
                labelA = new Label(6, i, "" + employee.getEmpEmail());
                sheetA.addCell(labelA);
                labelA = new Label(7, i, "" + employee.getAddress());
                sheetA.addCell(labelA);
                labelA = new Label(8, i, "" + employee.getEducation());
                sheetA.addCell(labelA);
                labelA = new Label(9, i, "" + employee.getEmpPosition());
                sheetA.addCell(labelA);
                labelA = new Label(10, i, "" + employee.getDepartment());
                sheetA.addCell(labelA);
                labelA = new Label(11, i, "" + employee.getBirthday());
                sheetA.addCell(labelA);
                i++;
            }
            workbook.write();
            workbook.close();
        } catch (Exception e) {
            System.out.println();
        }
    }

    /**
     * 文件下载功能
     * @param response 响应
     * @param fileName 下载的文件名
     */
    public static void downLoadFile(HttpServletResponse response, String fileName, File file) throws IOException {
        FileInputStream fileInputStream = null;
        ServletOutputStream outputStream= null;
        try {
            //将文件写入流中
            fileInputStream = new FileInputStream(file);
            //1、清空响应区
            response.reset();
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/octet-stream");
            response.setContentType("application/x-download");
            //3、下载的文件名  attachment 附带文件下载  online 在线打开
            response.addHeader("Content-Disposition", "attachment;fileName=" + new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1));
            //response.addHeader("Content-Length",""+file.length());
            //文件流循环写入到响应流中
            outputStream = response.getOutputStream();
            byte[] buffer = new byte[1024];
            int len;
            while ((len = fileInputStream.read(buffer)) > 0){
                outputStream.write(buffer,0,len);
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            outputStream.flush();
            fileInputStream.close();
            outputStream.close();
        }
    }


    /**
     * 读取Excel表格 信息
     * @param fileName 读取的文件名
     * @return 信息集合
     */
    public static ArrayList<ArrayList> readExcel(String fileName){
        ArrayList<ArrayList> staffs = new ArrayList<>();
        try {
            //获取要读取文件
            String resourcePath = ClassUtils.getDefaultClassLoader().getResource("").getPath();
            File file = new File(resourcePath + "/static/importFile/"+fileName);
            //创建一个workbook对象
            Workbook workbook = Workbook.getWorkbook(new FileInputStream(file));
            Sheet sheetA = workbook.getSheet(0);
            for (int i = 0; i < sheetA.getRows(); i++) {
                ArrayList staff = new ArrayList<>();
                for (int j = 0; j < sheetA.getColumns(); j++) {
                    Cell cell = sheetA.getCell(j, i);
                    staff.add(cell.getContents());
                }
                staffs.add(staff);
            }
            //读取文件后删除文件
            file.delete();
            return staffs;
        }catch (Exception e){
            return staffs;
        }
    }




}
